The present invention relates in general to an interface between a human user and a computerized relational database. More particularly, the invention is directed to a system and method for creating relational database queries using graphical tree structure depictions, to express WHERE and HAVING clauses.
The entry and retrieval of data via interactive terminals is now a common practice. Large volumes of data stored on magnetic disks, optical disks, or other contemporary non-volatile storage media, are accessed locally or through networks using terminals or functionally equivalent workstations or personal computers. Such prevalent uses of databases has created an environment in which the level of data processing skill possessed by an average user is disproportionally low in relation to the complexity of the hardware and software functions available to interact with the database. This is particularly true for relational databases, where the information content is significant yet the query mechanisms for defining the desired information involve esoteric data definitions and groupings. Though the technology of accessing information has progressed beyond the use of structured query language (SQL) statements to the use of query by example (QBE) and prompted query techniques, there remains a need for further simplification and ease of use.
Relational database query expressions with WHERE and HAVING clauses are well known to those who have a working knowledge of SQL. However, the expressions can become quite lengthy and lose their intuitive meaning as the number of predicates and relationships in the query increases. Thus, there has evolved a need for a system and method which allows a moderately skilled user to create and manipulate WHERE and HAVING clauses in relational database queries.
U.S. Pat. No. 4,506,326 teaches the use of query by example (QBE) to formulate structured query language (SQL) syntax. Unfortunately, the QBE approach is limited in application and does not provide in graphical or other pictorial form an intuitive representation of what the expression represents in relation to the information in the database.
The use of tree-like graphics to represent Boolean logic is not new. Tree representations are used in hypercard type applications and artificial intelligence depictions. The use of Boolean factor trees to represent SQL statements is discussed in an article entitled "Method of Detecting Atomic Boolean Factors" as appeared on pages 368-374 of the IBM Technical Disclosure Bulletin, Vol. 32, No. 5B, dated October 1989. Therein it is noted that WHERE and HAVING clauses of SQL statements can be represented by Boolean factor trees. However, it is also noted that Boolean factor trees are binary. Boolean factor trees have a structure which loses intuitive worth when the number of predicates associated with a logical operation exceeds two.
A representative relational database is a part of the OS/2 (trademark of IBM Corporation) Extended Edition operating system program, available from IBM Corporation, as particularly suited for use in a PS/2 (trademark of IBM Corporation) workstation. A preferred configuration for the workstation includes a graphics display, a keyboard, a mouse and cabinet resident hard disk drive. A typical workstation would also include communication and networking cards suitable to access remote databases or databases resident on host computers. In the context of such configurations, there exists a need for a system and method by which a user of moderate database expertise can define, manipulate and utilize a relational database query including those constructed with relatively complex WHERE and HAVING clauses.